package org.youyun56.gpsserver.db;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.youyun56.gpsserver.CacheFactory;
import org.youyun56.gpsserver.common.DateUtils;
import org.youyun56.gpsserver.common.Page;
import org.youyun56.gpsserver.common.StringUtils;
import org.youyun56.gpsserver.entity.Good;
import org.youyun56.gpsserver.entity.SmsType;

import com.j3.db.DataSourceUnRegisterException;
import com.j3.sql.BatchFunc;
import com.j3.sql.GridUtils;
import com.j3.sql.SqlFunc;

public class GoodDao {

    private final static String countCity = "select count(*) from good";
    private final static String findByCity = "select * from good";
    private final static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private final static String reverseGood = "select * from good where targetCity = %d and addTime > '%s'";
    private final static Logger logger = Logger.getLogger(GoodDao.class);

    public List<Good> reverseGood(Integer cityId) throws SQLException {
        GridUtils grid = new GridUtils();
        Calendar c = Calendar.getInstance();
        c.add(Calendar.DAY_OF_YEAR, -1);
        c.set(Calendar.MINUTE, 0);
        c.set(Calendar.SECOND, 0);
        c.set(Calendar.MILLISECOND, 0);
        c.set(Calendar.HOUR_OF_DAY, 18);
        grid.init(String.format(reverseGood, cityId, DateUtils.formatFull(c.getTime())));
        List<Good> goods = new ArrayList<Good>();
        while (grid.next()) {
            Good good = new Good();
            good.setAddTime(new Date(grid.getTimestamp("addTime").getTime()));
            good.setContact(grid.get("contact"));
            good.setFromCity(grid.getInt("fromCity"));
            good.setId(grid.getLong("id"));
            good.setTargetCity(grid.getInt("targetCity"));
            good.setToCity(grid.getInt("toCity"));
            good.setTxt(grid.get("txt"));
            good.setIndex(grid.getLong("indexx"));
            good.setUserId(grid.getLong("userId"));
            good.setCarType(grid.get("carType"));
            good.setCost(grid.getFloat("cost"));
            good.setCostTxt(grid.get("costText"));
            good.setCostUnin(grid.get("costUnin"));
            good.setHearvy(grid.getFloat("hearvy"));
            good.setHearvyUnin(grid.get("hearvyUnin"));
            good.setHeavyType(grid.get("hearvyType"));
            good.setLian(grid.getInt("lian"));
            good.setMi(grid.getFloat("mi"));
            goods.add(good);
        }
        return goods;
    }

    public List<Good> findByCity(Integer cityId, Page page) throws SQLException {
        SqlFunc fun = new SqlFunc();
        page.setTotalCount(Integer.parseInt(fun.getCol00(countCity)));
        GridUtils grid = new GridUtils();
        StringBuilder sb = new StringBuilder(findByCity);
        grid.init(String.format(
                sb.append(" where targetCity = %d and id <=(select id from good order by id desc limit %d,1) order by id  desc limit 0,%d")
                        .toString(), cityId, page.getFirstResult(), page.getPageSize()));
        List<Good> goods = new ArrayList<Good>();
        while (grid.next()) {
            Good good = new Good();
            good.setAddTime(new Date(grid.getTimestamp("addTime").getTime()));
            good.setContact(grid.get("contact"));
            good.setFromCity(grid.getInt("fromCity"));
            good.setId(grid.getLong("id"));
            good.setTargetCity(grid.getInt("targetCity"));
            good.setToCity(grid.getInt("toCity"));
            good.setTxt(grid.get("txt"));
            good.setIndex(grid.getLong("indexx"));
            good.setUserId(grid.getLong("userId"));
            good.setCarType(grid.get("carType"));
            good.setCost(grid.getFloat("cost"));
            good.setCostTxt(grid.get("costText"));
            good.setCostUnin(grid.get("costUnin"));
            good.setHearvy(grid.getFloat("hearvy"));
            good.setHearvyUnin(grid.get("hearvyUnin"));
            good.setHeavyType(grid.get("hearvyType"));
            good.setLian(grid.getInt("lian"));
            good.setMi(grid.getFloat("mi"));
            goods.add(good);
        }
        return goods;
    }

    // private final static String save =
    // "insert into Good(targetCity,txt,addTime,userId,fromCity,toCity,contact) values(?,?,?,?,?,?,?)";
    //
    // public int saveGood(Good good) throws SQLException {
    // SqlFunc fun = new SqlFunc();
    // List<Object> parameters = new ArrayList<Object>();
    // parameters.add(good.getTargetCity());
    // parameters.add(good.getTxt());
    // parameters.add(new Timestamp(System.currentTimeMillis()));
    // parameters.add(good.getUserId());
    // parameters.add(good.getFromCity());
    // parameters.add(good.getToCity());
    // parameters.add(good.getContact());
    // return fun.psUpdate(save, parameters,
    // Arrays.asList(Types.BIGINT, Types.VARCHAR, Types.TIMESTAMP, Types.BIGINT,
    // Types.BIGINT, Types.BIGINT, Types.VARCHAR));
    // }

    /**
     * id
     * 
     * 
     * 
     * 
     * 
     * 
     * 
     * 
     * hearvyType hearvy hearvyUnin lian mi carType costText cost costUnin
     * 
     * @param goods
     * @return
     * @throws SQLException
     */
    public Boolean saveGood(List<Good> goods) throws SQLException {
        BatchFunc fun = new BatchFunc();
        List<String> sqls = new ArrayList<String>();
        Object smscity = CacheFactory.props.get("smscity");
        for (Good good : goods) {
            StringBuilder sb = new StringBuilder(
                    "insert into good(targetCity,indexx,txt,addTime,userId,fromCity,toCity,contact,hearvyType,hearvy,hearvyUnin,lian,mi,carType,costText,cost,costUnin) values(");
            sb.append(good.getTargetCity());
            sb.append(",");
            sb.append(good.getIndex());
            sb.append(",'");
            sb.append(good.getTxt());
            sb.append("','");
            sb.append(DateUtils.formatFull(good.getAddTime()));
            sb.append("',");
            sb.append(good.getUserId());
            sb.append(",");
            sb.append(good.getFromCity());
            sb.append(",");
            sb.append(good.getToCity());
            sb.append(",");
            if (good.getContact() != null) {
                sb.append("'").append(good.getContact()).append("',");
            } else {
                sb.append("null,");
            }
            if (StringUtils.isEmpty(good.getHeavyType())) {
                sb.append("null,");
            } else {
                sb.append("'").append(good.getHeavyType()).append("',");
            }
            if (good.getHearvy() == null) {
                sb.append("null,");
            } else {
                sb.append(good.getHearvy()).append(",");
            }
            if (good.getHearvy() != null) {
                sb.append("'").append(good.getHearvyUnin()).append("',");
            } else {
                sb.append("null,");
            }
            if (good.getLian() == null) {
                sb.append("null,");
            } else {
                sb.append(good.getLian()).append(",");
            }
            if (good.getMi() == null) {
                sb.append("null,");
            } else {
                sb.append(good.getMi()).append(",");
            }
            if (good.getCarType() == null) {
                sb.append("null,");
            } else {
                sb.append("'").append(good.getCarType()).append("',");
            }
            if (good.getCostTxt() == null) {
                sb.append("null,");
            } else {
                sb.append("'").append(good.getCostTxt()).append("',");
            }
            if (good.getCost() == null) {
                sb.append("null,");
            } else {
                sb.append("'").append(good.getCost()).append("',");
            }
            if (good.getCostUnin() == null) {
                sb.append("null");
            } else {
                sb.append("'").append(good.getCostUnin()).append("'");
            }
            sb.append(")");
            sqls.add(sb.toString());
            if (smscity != null && smscity.toString().trim().length() > 0) {
                String[] citys = smscity.toString().trim().split(",");
                for (String city : citys) {
                    if (good.getTargetCity().equals(Integer.valueOf(city)) && good.getUserId() != null) {
                        sqls.add(createSmsSql(good));
                        break;
                    }
                }
            }
        }
        try {
            return fun.doBatch(sqls);
        }
        catch (SQLException e) {
            logger.error(sqls, e);
            throw (e);
        }
    }

    private String createSmsSql(Good good) {
        StringBuilder sb = new StringBuilder();
        sb.append("insert into sms(mobiles,context,smsType,user,addDay,done) values(");
        sb.append(good.getTargetCity());
        sb.append(",'");
        sb.append(good.getTxt()).append(" ").append(good.getContact());
        sb.append("','");
        sb.append(SmsType.TOCITYCARS.name());
        sb.append("',");
        sb.append(good.getUserId());
        sb.append(",'");
        sb.append(sdf.format(new Date(good.getAddTime().getTime())));
        sb.append("',0)");
        return sb.toString();
    }

    public Integer getMaxIndex(Integer city) throws DataSourceUnRegisterException, SQLException {
        SqlFunc fun = new SqlFunc();
        String index = fun.getCol00(String.format("select max(indexx) from good where targetCity = %d", city));
        return index == null ? 0 : Integer.valueOf(index);
    }
}
